<?php

class vo_ecommerce extends CActiveRecord {

    //data products category
    public static function get_products_category($type = '') {
        $sql = "SELECT products_category_title FROM ecom_products_category";
        if ($type != '')
            $sql .= " WHERE products_category_type = '" . $type . "'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //get products category prefix
    public static function get_products_category_prefix($products_category_id) {
        $sql = "SELECT products_category_prefix FROM ecom_products_category WHERE products_category_id = '".$products_category_id."' LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return $result;
    }

    //data products berdasarkan id kategori
    public static function get_products_by_category_id($category_id = '') {
        $sql = "SELECT * FROM ecom_products 
            WHERE products_products_category_id = '" . $category_id . "' 
            ORDER BY products_products_category_id";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //data products berdasarkan tipe kategori
    public static function get_products_by_category_type($category_type = '') {
        $sql = "SELECT ecom_products.*, ecom_products_category.products_category_id, ecom_products_category.products_category_title 
            FROM ecom_products, ecom_products_category 
            WHERE products_products_category_id = products_category_id 
            AND products_category_type = '" . $category_type . "' 
            ORDER BY products_products_category_id";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //data products berdasarkan id
    public static function get_products_by_id($products_id) {
        $sql = "SELECT * FROM ecom_products WHERE products_id = '" . $products_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        return $result;
    }

    //cari urutan kode transaksi per hari
    public static function get_num_transaction_today() {
        $sql = "SELECT COUNT(*) FROM ecom_transaction WHERE LEFT(transaction_code, 6) = '" . date("ymd") . "'";
        $result = Yii::app()->db->createCommand($sql)->queryScalar();
        return ($result + 1);
    }

    //cari serial produk yang available
    public static function get_serial_products($products_category_id) {
        $sql = "SELECT * 
            FROM sys_serial_products 
            WHERE serial_products_is_active = '0' 
            AND serial_products_is_buyed = '0' 
            AND serial_products_is_used = '0' 
            AND serial_products_products_category_id = '" . $products_category_id . "'
            LIMIT 1";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        return $result;
    }

    //data transaksi
    public static function get_transaction($network_id) {
        $sql = "SELECT *, (select (sum(transaction_detail_quantity* transaction_detail_bv)) from ecom_transaction_detail where transaction_detail_transaction_id = ecom_transaction.transaction_id) as total_bv
            FROM ecom_transaction 
            WHERE transaction_buyer_network_id = '" . $network_id . "' 
            ORDER BY transaction_datetime DESC";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //data transaksi berdasarkan id transaksi
    public static function get_transaction_by_id($network_id, $transaction_code) {
        $sql = "SELECT *,(select (sum(transaction_detail_quantity* transaction_detail_bv)) from ecom_transaction_detail where transaction_detail_transaction_id = ecom_transaction.transaction_id) as total_bv
             
            FROM ecom_transaction 
            WHERE transaction_code = '" . $transaction_code . "' 
            AND transaction_buyer_network_id = '" . $network_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        return $result;
    }

    //data detail transaksi
    public static function get_transaction_detail($transaction_id) {
        $sql = "SELECT * FROM ecom_transaction_detail WHERE transaction_detail_transaction_id = '" . $transaction_id . "'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //data serial yang dibeli
    public static function get_serial_products_buyer($network_id, $used = '') {
        $sql = "SELECT * 
            FROM sys_serial_products_buyer 
            JOIN sys_serial_products ON serial_products_id = serial_products_buyer_serial_products_id 
            JOIN ecom_products_category ON products_category_id = serial_products_products_category_id 
            WHERE serial_products_buyer_network_id = '" . $network_id . "' ";
        if($used != '') {
            $sql .= "AND serial_products_is_used = '".$used."' ";
        }
        $sql .= "ORDER BY serial_products_products_category_id";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }

    //data serial request
    public static function get_serial_products_request($network_id) {
        $sql = "SELECT * 
            FROM sys_serial_products_request 
            JOIN ecom_products_category ON products_category_id = serial_products_request_products_category_id 
            WHERE serial_products_request_network_id = '" . $network_id . "'
            AND serial_products_request_is_accepted = '0' 
            ORDER BY serial_products_request_products_category_id";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        return $result;
    }
    
    public static function serial_products_validation($serial, $pin) {
        $sql = "SELECT * FROM sys_serial_products WHERE serial_products_id = '".$serial."' AND serial_products_pin = '".$pin."'";
        $result = Yii::app()->db->createCommand($sql)->queryRow();
        
        return $result;
    }
    
    

}